﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace QL_Nha_Sach_Phuong_Nam.Kết_nối
{
    class CDuLieu
    {
        private static CDuLieu m_data = null;
        private KetNoiDataContext m_ds;
        private CDuLieu()
        {
            string strCon = @"Data Source=THANHVY-PC\SQLEXPRESS;Initial Catalog=NhaSachPhuongNam;Integrated Security=True";
            m_ds = new KetNoiDataContext(strCon);
        }
        public static CDuLieu khoitao()
        {
            if (m_data == null) m_data = new CDuLieu();
            return m_data;
        }

        /// <summary>
        /// Get Du Lieu
        /// </summary>
        /// <returns></returns>
        /// 

        #region Các hàm xử lý của tác giả

        //Tra ve ds tac gia
        public List<TacGia> getdsTacGia()
        {
            return m_ds.TacGias.ToList();
        }

        //tim tac gia theo ma
        private TacGia TimTacGiama(string matg)
        {
            TacGia tg = null;
            var kq = m_ds.TacGias.Where(x => x.TacGiaNo == matg);
            if (kq.Count() > 0)
                tg = kq.First();
            return tg;
        }

        //Them tac gia
        public void ThemTacgia(TacGia tg)
        {
            m_ds.TacGias.InsertOnSubmit(tg);
            updateDulieu();
        }

        //Xoa tac gia
        public void XoaTacgia(string ma)
        {
            TacGia tg = m_ds.TacGias.Where(x=>x.TacGiaNo==ma).First();
            if (tg != null)
            {
                tg.TacGiaStatus = "delete";
                updateDulieu();
            }
        }

        //Sua tac gia
        public void SuaTacgia(TacGia tg)
        {
            Kết_nối.TacGia edittg = m_ds.TacGias.Where(x => x.TacGiaNo == tg.TacGiaNo).First();
            if (edittg != null)
            {
                edittg.TacGiaName = tg.TacGiaName;
                edittg.TacGiaSex = tg.TacGiaSex;
                edittg.TacGiaAddress = tg.TacGiaAddress;
                edittg.TacGiaPhoneNumber = tg.TacGiaPhoneNumber;
                edittg.TacGiaDescription = tg.TacGiaDescription;
                updateDulieu();
            }
        }

        #endregion

        #region Các hàm xử lý của thể loại

        //Lay ds the loai
        public List<TheLoai> getdsTheLoai()
        {
            return m_ds.TheLoais.ToList();
        }

        //tim the loai theo ma
        private TheLoai TimTheLoaima(string matl)
        {
            TheLoai tl =null;
            var kq = m_ds.TheLoais.Where(x => x.TheLoaiNo == matl);
            if (kq.Count() > 0)
                tl = kq.First();
            return tl;
        }

        //Them the loai
        public void ThemTheLoai(TheLoai tl)
        {
            m_ds.TheLoais.InsertOnSubmit(tl);
            updateDulieu();
        }

        //Xoa the loai
        public void XoaTheLoai(string tentl)
        {
            TheLoai tl = m_ds.TheLoais.Where(x => x.TheLoaiName == tentl).First();
            if (tl != null)
            {
                tl.TheLoaiStatus = "delete";
            }
            updateDulieu();
        }

        //Sua the loai
        public void SuaTheLoai(TheLoai tl)
        {
            TheLoai tledit = m_ds.TheLoais.Where(x => x.TheLoaiNo == tl.TheLoaiNo).First();
            if (tledit != null)
            {
                tledit.TheLoaiNo = tl.TheLoaiNo;
                tledit.TheLoaiName = tl.TheLoaiName;
                tledit.TheLoaiDescription = tl.TheLoaiDescription;
            }
            updateDulieu();
        }

        #endregion

        #region Các hàm xử lý của thông tin sách
        
        //Lay danh sach Sach
        public List<Sach> getdsSach()
        {
            return m_ds.Saches.ToList();
        }

        //tim theo ma sach
        private Sach TimSachtheoma(string ma)
        {
            Sach sach = null;
            var kq = m_ds.Saches.Where(x => x.SachNo == ma);
            if (kq.Count() > 0)
            {
                sach = kq.First();
            }
            return sach;
        }

        //Tim sach theo Id
        private Sach TimSachtheoId(int id)
        {
            Sach sach = null;
            var kq = m_ds.Saches.Where(x => x.SachID == id);
            if (kq.Count() > 0)
            {
                sach = kq.First();
            }
            return sach;
        }

        //them sach
        public void ThemSach(Kết_nối.Sach sach)
        {
            m_ds.Saches.InsertOnSubmit(sach);
            foreach (Kết_nối.Sach_TacGia stg in sach.Sach_TacGias)
            {
                m_ds.Sach_TacGias.InsertOnSubmit(stg);
            }
            foreach (Kết_nối.Sach_TheLoai stl in sach.Sach_TheLoais)
            {
                m_ds.Sach_TheLoais.InsertOnSubmit(stl);
            }
            updateDulieu();
        }

        //xoa sach
        public void XoaSach(string ma)
        {
            Sach sach = TimSachtheoma(ma);
            if (sach != null)
            {
                sach.SachStatus = "delete";
                updateDulieu();
            }
        }


        //sua sach
        public void SuaSach(Sach sach, string manxb)
        {
            Sach editSach = TimSachtheoId(sach.SachID);
            NhaXuatBan nxb = TimNhaXuatBanma(manxb);
            if (sach != null && nxb != null)
            {
                foreach (Kết_nối.Sach_TacGia stg in editSach.Sach_TacGias)
                {
                    m_ds.Sach_TacGias.DeleteOnSubmit(stg);
                }
                foreach (Kết_nối.Sach_TheLoai stl in editSach.Sach_TheLoais)
                {
                    m_ds.Sach_TheLoais.DeleteOnSubmit(stl);
                }
                updateDulieu();
                editSach.SachNo = sach.SachNo;
                editSach.SachName = sach.SachName;
                editSach.SachNamXuatBan = sach.SachNamXuatBan;
                editSach.SachPrice = sach.SachPrice;
                editSach.SachDescription = sach.SachDescription;
                editSach.NhaXuatBan = nxb;
                foreach (Kết_nối.Sach_TacGia stg1 in sach.Sach_TacGias)
                {
                    Sach_TacGia test = new Sach_TacGia();
                    test.Sach_TacGiaDescription = stg1.Sach_TacGiaDescription;
                    test.TacGia = TimTacGiama(stg1.TacGia.TacGiaNo);
                    test.Sach = editSach;
                    m_ds.Sach_TacGias.InsertOnSubmit(test);
                }
                foreach (Kết_nối.Sach_TheLoai stl1 in sach.Sach_TheLoais)
                {
                    Sach_TheLoai test2 = new Sach_TheLoai();
                    test2.Sach_TheLoaiDescription = stl1.Sach_TheLoaiDescription;
                    test2.TheLoai = TimTheLoaima(stl1.TheLoai.TheLoaiNo);
                    test2.Sach = editSach;
                    m_ds.Sach_TheLoais.InsertOnSubmit(test2);
                }
                updateDulieu();
            }
        }

        #endregion

        #region Các hàm xử lý của nhà xuất bản
        
        //Lay danh sach NXB
        public List<NhaXuatBan> getdsNXB()
        {
            return m_ds.NhaXuatBans.ToList();
        }

        //tim nha xuat ban theo ma
        private NhaXuatBan TimNhaXuatBanma(string manhaXuatBan)
        {
            NhaXuatBan nxb = null;
            var kq = m_ds.NhaXuatBans.Where(x => x.NhaXuatBanNo == manhaXuatBan);
            if (kq.Count() > 0)
                nxb = kq.First();
            return nxb;
        }

        //Them nha xuat ban
        public void ThemNhaXuatBan(NhaXuatBan nxb)
        {
            m_ds.NhaXuatBans.InsertOnSubmit(nxb);
            updateDulieu();
        }

        //Xoa nha xuat ban
        public void XoaNhaXuatBan(string manxb)
        {
            NhaXuatBan editnxb = TimNhaXuatBanma(manxb);
            if (editnxb != null)
            {
                editnxb.NhaXuatBanStatus = "delete";
                updateDulieu();
            }

        }

        //Sua nha xuat ban
        public void SuaNhaXuatBan(Kết_nối.NhaXuatBan nxb)
        {
            NhaXuatBan editnxb = TimNhaXuatBanma(nxb.NhaXuatBanNo);
            if (editnxb != null)
            {
                editnxb.NhaXuatBanName = nxb.NhaXuatBanName;
                editnxb.NhaXuatBanAddress = nxb.NhaXuatBanAddress;
                editnxb.NhaXuatBanPhoneNumber = nxb.NhaXuatBanPhoneNumber;
                editnxb.NhaXuatBanEmail = nxb.NhaXuatBanEmail;
                editnxb.NhaXuatBanDescription = nxb.NhaXuatBanDescription;
                updateDulieu();
            }
        }

        #endregion

        #region Các hàm xử lý phiếu đặt

        //lay danh sach phieu dat
        public List<PhieuDat> getListPhieuDat()
        {
            return m_ds.PhieuDats.ToList();
        }

        //tim phieu dat
        public PhieuDat TimPhieuDat(string maphieudat)
        {
            PhieuDat pd=null;
            var kq = m_ds.PhieuDats.Where(x => x.PhieuDatNo == maphieudat);
            if (kq.Count()>0)
                pd = kq.First();
            return pd;
        }
        
        //them phieu dat
        public void ThemPhieuDat(PhieuDat pd)
        {
            m_ds.PhieuDats.InsertOnSubmit(pd);
            updateDulieu();
        }

        //xoa phieu dat
        public void XoaPhieuDat(string maphieudat)
        {
            PhieuDat pd = TimPhieuDat(maphieudat);
            if (pd != null)
            {
                pd.PhieuDatStatus = "delete";
                updateDulieu();
            }
        }

        //sua phieu dat
        public void SuaPhieuDat(PhieuDat pd)
        {
            PhieuDat editpd = TimPhieuDat(pd.PhieuDatNo);
            if (editpd != null)
            {
                //delete luôn chi tiết phiếu
                foreach (ChiTietPhieuDat ctpd in editpd.ChiTietPhieuDats)
                {
                    m_ds.ChiTietPhieuDats.DeleteOnSubmit(ctpd);
                }
                updateDulieu();

                editpd.PhieuDatNo = pd.PhieuDatNo;
                editpd.PhieuDatPayment = pd.PhieuDatPayment;
                editpd.PhieuDatDate = pd.PhieuDatDate;
                editpd.PhieuDatDeliveTime = pd.PhieuDatDeliveTime;
                
                //Them lai chi tiet phieu
                foreach (ChiTietPhieuDat ctpd in pd.ChiTietPhieuDats)
                {
                    ChiTietPhieuDat ct = new ChiTietPhieuDat();
                    ct.Sach=TimSachtheoId(ctpd.FK_SachID);
                    ct.PhieuDat = TimPhieuDat(ctpd.PhieuDat.PhieuDatNo);
                    ct.ChiTietPhieuDatQuantum = ctpd.ChiTietPhieuDatQuantum;
                    ct.ChiTietPhieuDatPrice = ctpd.ChiTietPhieuDatPrice;
                    ct.ChiTietPhieuDatTotal = ctpd.ChiTietPhieuDatTotal;
                    m_ds.ChiTietPhieuDats.InsertOnSubmit(ct);
                }
                updateDulieu();
            }
        }

        //them chi tiet phieu dat
        public void ThemChitietPhieuDat(PhieuDat pd)
        {
            foreach (ChiTietPhieuDat ctpd in pd.ChiTietPhieuDats)
            {
                m_ds.ChiTietPhieuDats.InsertOnSubmit(ctpd);
                updateDulieu();
            }
        }

        #endregion

        #region Các hàm xử lý của phiếu nhập

        public List<PhieuNhap> getDsPhieuNhap()
        {
            return m_ds.PhieuNhaps.ToList();
        }

        public Table<ChiTietPhieuNhap> getTableChiTietPhieuNhap()
        {
            return m_ds.ChiTietPhieuNhaps;
        }

        #endregion

        #region Các hàm xử lý của nhà cung cấp

        //lay ds nha cung cap
        public List<NhaCungCap> getdsNCC()
        {
            return m_ds.NhaCungCaps.ToList();
        }

        //Tim nha cung cap
        private NhaCungCap TimNCC(string ma)
        {
            NhaCungCap ncc = null;
            var kq = m_ds.NhaCungCaps.Where(x => x.NhaCungCapNo == ma);
            if (kq.Count() > 0)
                ncc = kq.First();
            return ncc;
        }

        //Them nha cung cap
        public void ThemNhacungcap(Kết_nối.NhaCungCap ncc)
        {
            m_ds.NhaCungCaps.InsertOnSubmit(ncc);
            updateDulieu();
        }

        //Xoa nha cung cap
        public void XoaNhacungcap(string mancc)
        {
            NhaCungCap editncc = m_ds.NhaCungCaps.Where(x=>x.NhaCungCapNo==mancc).First();
            if (editncc != null)
            {
                editncc.Nhacungcapstatus = "delete";
                updateDulieu();
            }
        }

        //Sua nha cung cap
        public void SuaNhacungcap(NhaCungCap ncc)
        {
            NhaCungCap editncc = m_ds.NhaCungCaps.Where(x => x.NhaCungCapNo == ncc.NhaCungCapNo).First();
            if (editncc != null)
            {
                editncc.NhaCungCapName = ncc.NhaCungCapName;
                editncc.NhaCungCapAddress = ncc.NhaCungCapAddress;
                editncc.NhaCungCapPhoneNumber = ncc.NhaCungCapPhoneNumber;
                editncc.NhaCungCapEmail = ncc.NhaCungCapEmail;
                editncc.NhaCungCapDescription = ncc.NhaCungCapDescription;
                updateDulieu();
            }
        }

        //them danh muc sach
        public void ThemDanhMucSach(NhaCungCap ncc, string ma)
        {
            Sach_NhaCungCap sncc = new Sach_NhaCungCap();
            sncc.NhaCungCap = TimNCC(ncc.NhaCungCapNo);
            sncc.Sach = TimSachtheoma(ma);
            m_ds.Sach_NhaCungCaps.InsertOnSubmit(sncc);
        }

        #endregion

        /// <summary>
        /// update Du Lieu
        /// </summary>
        private void updateDulieu()
        {
            m_ds.SubmitChanges();
        }
    }
}
